package org.jxls.demo;

import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.jxls.area.Area;
import org.jxls.area.XlsArea;
import org.jxls.builder.AreaBuilder;
import org.jxls.builder.xml.XmlAreaBuilder;
import org.jxls.command.EachCommand;
import org.jxls.command.GridCommand;
import org.jxls.common.AreaListener;
import org.jxls.common.AreaRef;
import org.jxls.common.CellRef;
import org.jxls.common.Context;
import org.jxls.formula.StandardFormulaProcessor;
import org.jxls.transform.Transformer;
import org.jxls.transform.poi.PoiTransformer;
import org.jxls.util.TransformerFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.*;

/**
 * Created by Leonid Vysochyn on 24-Jun-15.
 */
public class CustomDynamicColumnsDemo {
    static Logger logger = LoggerFactory.getLogger(CustomDynamicColumnsDemo.class);

    private static final String TEMPLATE = "PRPReportTemplate.xlsx";
    private static final String OUTPUT = "target/PRPReportTemplate.xlsx";

    public static void main(String[] args) throws IOException {
        logger.info("Running Dynamic Columns demo");
        execute();
    }

    public static void execute() throws IOException {
        logger.info("Preparing test data");
        List<String> headers = new ArrayList<String>(Arrays.asList("Month", "CR Rate", "PPC Cnt Num", "Searchers", "Files Qu", "SRC Score"));
        List<List<Object>> rows = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            List<Object> row = new ArrayList<>();
            for (int k = 0; k < 5; k++) {
                if(k == 0){
                    row.add("Dec-201"+i);
                    row.add(Math.random()*10 + "%");
                    row.add(Math.random()*10 + "%");
                    row.add(Math.floor(Math.random()*1000));
                    row.add("$ " + (int) Math.random()*10000);
                    row.add(new BigDecimal(Math.random()));
                }
            }
            rows.add(row);
        }

        // loading areas and commands using XmlAreaBuilder
        try (InputStream is = CustomDynamicColumnsDemo.class.getResourceAsStream(TEMPLATE)) {
            try (OutputStream os = new FileOutputStream(OUTPUT)) {
                Transformer transformer = TransformerFactory.createTransformer(is, os);
                Workbook workbook = ((PoiTransformer) transformer).getWorkbook();
                Sheet sheet = workbook.getSheetAt(0);
                XlsArea xlsArea = new XlsArea("Result!A1:B12", transformer);
                Map data = new HashMap();
                data.put("tableHeader", "Jonathan-MTD-Report-01/17/2018");
                data.put("createdOn", "01/17/2018");
                data.put("accountName", "Jonathan Zhang");
                data.put("startDate", "02/01/2016");
                data.put("endDate", "05/31/2016");
                data.put("note", "Data is not final! you must contact your admin to get you full data from S3.");
                GridCommand gridCommand = new GridCommand();
                gridCommand.setHeaders("headers");
                Area headerArea = new XlsArea("Result!A10:A10", transformer);
                Area bodyArea = new XlsArea("Result!A11:A11", transformer);
                bodyArea.addAreaListener(new AreaListener() {
                    @Override
                    public void beforeApplyAtCell(CellRef cellRef, Context context) {

                    }

                    @Override
                    public void afterApplyAtCell(CellRef cellRef, Context context) {

                    }

                    @Override
                    public void beforeTransformCell(CellRef cellRef, CellRef cellRef1, Context context) {

                    }

                    @Override
                    public void afterTransformCell(CellRef cellRef, CellRef cellRef1, Context context) {
                        if (cellRef1.getCol() == 4) {
                            Cell cell = sheet.getRow(cellRef1.getRow()).getCell(cellRef1.getCol());
                            CellStyle cellStyle = cell.getCellStyle();
//                            CellStyle newCellStyle = workbook.createCellStyle();
                            cellStyle.setAlignment(HorizontalAlignment.RIGHT);
//                            Font font = workbook.getFontAt(cellStyle.getFontIndex());
////                            font.setColor(IndexedColors.ORANGE.getIndex());
//                            System.out.println(font.getColor());
//                            newCellStyle.setFont(font);
//                            newCellStyle.setFillBackgroundColor(cellStyle.getFillBackgroundColor());
//                            newCellStyle.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
//                            newCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                            cell.setCellStyle(cellStyle);
                        }
//                        if (cellRef1.getCol() == 1) {
//                            Cell cell = sheet.getRow(cellRef1.getRow()).getCell(cellRef1.getCol());
//                            CellStyle cellStyle = cell.getCellStyle();
//                            cellStyle.
////                            dataFormatter.formatCellValue(cell);
//                            cellStyle.setDataFormat();
//                            cell.setCellStyle(cellStyle);
//                        }
                    }
                });
                gridCommand.addArea(headerArea).addArea(bodyArea);
                gridCommand.setData("rows");
                xlsArea.addCommand("A10:B10", gridCommand);

                EachCommand footerEachCommand = new EachCommand();
                footerEachCommand.setDirection(EachCommand.Direction.RIGHT);
                footerEachCommand.setItems("footer");
                footerEachCommand.setVar("fv");
                XlsArea footerArea = new XlsArea("Result!B12:B12", transformer);
                footerArea.addAreaListener(new AreaListener() {
                    @Override
                    public void beforeApplyAtCell(CellRef cellRef, Context context) {

                    }

                    @Override
                    public void afterApplyAtCell(CellRef cellRef, Context context) {

                    }

                    @Override
                    public void beforeTransformCell(CellRef cellRef, CellRef cellRef1, Context context) {

                    }

                    @Override
                    public void afterTransformCell(CellRef cellRef, CellRef cellRef1, Context context) {
                        if (cellRef1.getCol() == 4) {
                            Cell cell = sheet.getRow(cellRef1.getRow()).getCell(cellRef1.getCol());
                            CellStyle cellStyle = cell.getCellStyle();
//                            CellStyle newCellStyle = workbook.createCellStyle();
                            cellStyle.setAlignment(HorizontalAlignment.RIGHT);
//                            Font font = workbook.getFontAt(cellStyle.getFontIndex());
////                            font.setColor(IndexedColors.ORANGE.getIndex());
//                            System.out.println(font.getColor());
//                            newCellStyle.setFont(font);
//                            newCellStyle.setFillBackgroundColor(cellStyle.getFillBackgroundColor());
//                            newCellStyle.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
//                            newCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                            cell.setCellStyle(cellStyle);
                        }
                    }
                });
                footerEachCommand.addArea(footerArea);
                xlsArea.addCommand("B12:B12", footerEachCommand);
                // creating context
                Context context = PoiTransformer.createInitialContext();
                context.putVar("headers", headers);
                context.putVar("rows", rows);
                context.putVar("data", data);
                context.putVar("footer", new ArrayList(){{add(123);add("");add(123);add("0.234%");add("123");}});
                // applying transformation
                logger.info("Applying area " + xlsArea.getAreaRef() + " at cell " + new CellRef("Result!A1"));

                xlsArea.applyAt(new CellRef("Result!A1"), context);
                workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(0, 0, 0, headers.size() - 1));
                CellStyle cs = workbook.getSheetAt(0).getRow(0).getCell(0).getCellStyle();
                cs.setAlignment(HorizontalAlignment.CENTER);
                transformer.write();
                logger.info("Complete");
            }
        }
    }
}
